/**
 * CodeUtil
 * 작성자 : 윤화현
 * 날  짜 : 2012.9.20
 * 소  속 : 
 * 프로젝트에 필요한 코드들을 모아둔 곳입니다.
 * DB 쿼리문, Xml 파싱, Assets 폴더 자료 로드 및 내부에 파일 저장
 * 
 */

package com.dnbns.lib;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.params.HttpConnectionParams;
import org.apache.http.params.HttpParams;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.xmlpull.v1.XmlPullParserException;

import android.annotation.SuppressLint;
import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.dnbns.common.StaticValue;

public class CodeUtil {

	private static CodeUtil mInstance;
	private Context mContext;
	private DBbase _db;
	private AlarmDB aDB;
	
	public CodeUtil(Context mContext){
		this.mContext = mContext;
		_db = new DBbase(mContext);
		aDB = new AlarmDB(mContext);
	}

	public static CodeUtil getInstance(Context activity){
		mInstance = new CodeUtil(activity);
		return mInstance;
	}
		
	public boolean dbExists(String table){
		String selectQuery = "select * from " + table;
		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		if(cursor.getCount() == 0){
			cursor.close();
			db.close();
			return false;
		}else{
			cursor.close();
			db.close();
			return true;
		}
	}
	
	public JSONArray execute(String query){
		return _db.execute(query);
	}
	
	public JSONArray execute2(String query){
		return aDB.execute(query);
	}
	
	public void dropAllTable() throws Exception{
		SQLiteDatabase db = _db.getWritableDatabase();
		db.delete(DBbase.TABLE_WATER, null, null);
		db.close();
		
	}
	
	public void dropTable(String table) throws Exception{
		SQLiteDatabase db = _db.getWritableDatabase();
		db.delete(table, null, null);
		db.close();
	}
	
	//입력
	public boolean insertAllCode(JSONArray obj, int code) throws JSONException{
		SQLiteDatabase db = _db.getWritableDatabase();
		Log.i("TEST", "트랜젝션 시작.");
		db.beginTransaction();

		try{
			for(int i=0; i<obj.length(); i++){
				ContentValues values = new ContentValues();
				switch (code) {
				//물먹은거잉
				case 1000:				
					values.put("weight",	obj.getJSONObject(i).getInt("weight"));
					values.put("sex",		obj.getJSONObject(i).getBoolean("sex"));
					values.put("birth",		obj.getJSONObject(i).getString("birth"));
					values.put("cup_type",	obj.getJSONObject(i).getString("cup_type"));
					values.put("cup_vol",	obj.getJSONObject(i).getInt("cup_vol"));
					values.put("year",		obj.getJSONObject(i).getInt("year"));
					values.put("month",		obj.getJSONObject(i).getInt("month"));
					values.put("day",		obj.getJSONObject(i).getInt("day"));
					values.put("hour",		obj.getJSONObject(i).getInt("hour"));
					values.put("minute",	obj.getJSONObject(i).getInt("minute"));
					values.put("second",	obj.getJSONObject(i).getInt("second"));
					values.put("flag",		"1");
					values.put("server",	"0");
					db.insertOrThrow(DBbase.TABLE_WATER, null, values);
					break;
				default:
					break;
				}
			}
			
			db.setTransactionSuccessful();
		}catch(Exception e){
			e.printStackTrace();
			Log.e("TEST", "Error : " + e.getMessage());
			return false;
		}finally{
			db.endTransaction();
			Log.i("TEST", "트랜젝션 종료.");
		}
		db.close();
		return true;
	}
	
	//업데이트
	public boolean updateAllCode(JSONArray obj, int code, String codes) throws JSONException{
		SQLiteDatabase db = _db.getWritableDatabase();
		Log.i("TEST", "트랜젝션 시작.");
		db.beginTransaction();

		try{
			for(int i=0; i<obj.length(); i++){
				ContentValues values = new ContentValues();
				switch (code) {
				//물먹기.
				case 1000:
					values.put("server",	"1");
					db.update(DBbase.TABLE_WATER, values, "idx=?", new String[]{obj.getJSONObject(i).getInt("idx")+""});
					break;
				default:
					break;
				}
			}
			
			db.setTransactionSuccessful();
		}catch(Exception e){
			e.printStackTrace();
			Log.e("TEST", "Error : " + e.getMessage());
			return false;
		}finally{
			db.endTransaction();
			Log.i("TEST", "트랜젝션 종료.");
		}
		db.close();
		return true;
	}

	//삭제
	public boolean deleteCode(int code, String codes) throws JSONException{
		SQLiteDatabase db = _db.getWritableDatabase();
		Log.i("TEST", "트랜젝션 시작");
		db.beginTransaction();

		try{
			switch (code) {
			case 1000:
				db.delete(DBbase.TABLE_WATER, "idx=?", new String[]{codes});
				break;
			}
			
			db.setTransactionSuccessful();
		}catch(Exception e){
			e.printStackTrace();
			Log.e("TEST", "Error : " + e.getMessage());
			return false;
		}finally{
			db.endTransaction();
			Log.i("TEST", "트랜젝션 종료");
		}
		db.close();
		return true;
	}

/**
 * 알람 관련 데이터베이스 CRUD
 */

	//입력
	public boolean insertAllCode2(JSONArray obj, int code) throws JSONException{
		SQLiteDatabase db = aDB.getWritableDatabase();
		Log.i("TEST", "트랜젝션 시작.");
		db.beginTransaction();

		try{
			for(int i=0; i<obj.length(); i++){
				ContentValues values = new ContentValues();
				switch (code) {
				//물먹은거잉
				case 2000:				
					values.put("hour",		obj.getJSONObject(i).getString("hour"));
					values.put("minute",	obj.getJSONObject(i).getString("minute"));
					db.insertOrThrow(AlarmDB.TABLE_ALARM, null, values);
					break;
				default:
					break;
				}
			}
			
			db.setTransactionSuccessful();
		}catch(Exception e){
			e.printStackTrace();
			Log.e("TEST", "Error : " + e.getMessage());
			return false;
		}finally{
			db.endTransaction();
			Log.i("TEST", "트랜젝션 종료.");
		}
		db.close();
		return true;
	}
	
	//업데이트
	public boolean updateAllCode2(JSONArray obj, int code, String codes) throws JSONException{
		SQLiteDatabase db = aDB.getWritableDatabase();
		Log.i("TEST", "트랜젝션 시작.");
		db.beginTransaction();

		try{
			for(int i=0; i<obj.length(); i++){
				ContentValues values = new ContentValues();
				switch (code) {
				//물먹기.
				case 2000:
					values.put("hour",		obj.getJSONObject(i).getString("hour"));
					values.put("minute",	obj.getJSONObject(i).getString("minute"));
					db.update(AlarmDB.TABLE_ALARM, values, "idx=?", new String[]{codes});
					break;
				default:
					break;
				}
			}
			
			db.setTransactionSuccessful();
		}catch(Exception e){
			e.printStackTrace();
			Log.e("TEST", "Error : " + e.getMessage());
			return false;
		}finally{
			db.endTransaction();
			Log.i("TEST", "트랜젝션 종료.");
		}
		db.close();
		return true;
	}
	
	//삭제
	public boolean deleteCode2(int code, String codes) throws JSONException{
		SQLiteDatabase db = aDB.getWritableDatabase();
		Log.i("TEST", "트랜젝션 시작");
		db.beginTransaction();

		try{
			switch (code) {
			case 2000:
				db.delete(AlarmDB.TABLE_ALARM, "idx=?", new String[]{codes});
				break;
			}
			db.setTransactionSuccessful();
		}catch(Exception e){
			e.printStackTrace();
			Log.e("TEST", "Error : " + e.getMessage());
			return false;
		}finally{
			db.endTransaction();
			Log.i("TEST", "트랜젝션 종료");
		}
		db.close();
		return true;
	}

/**
 * =================================================================================
 * 
 * 1번은 쿼리 이름만을 이용해 데이터를 로드한다.
 * 2번은 1번에서 가져온 데이터의 코드를 이용해 하위분류를 재검사 한다.
 *  
 * 1. getQuery(String queryName)
 * 2. getQuery(String queryName, String code)
 * 
 * @param queryName
 * @return
 * =================================================================================
 */

	public JSONArray getQuery(String queryName){
		return getQuery(queryName, "", "", "");
	}
	
	public JSONArray getQuery(String queryName, String code){
		return getQuery(queryName, code, "", "");
	}

	public JSONArray getQuery(String queryName, String code, String code2){
		return getQuery(queryName, code, code2, "");
	}
	
	public JSONArray getQuery(String queryName, String code, String code2, String code3){

		String query = "";
		JSONArray cs = null;

		if(queryName.equals("water")){
	    	Calendar cal = Calendar.getInstance();
			query = "SELECT * FROM "+DBbase.TABLE_WATER
					+" where year='"+cal.get(Calendar.YEAR)+"'"
					+" and month='"+(cal.get(Calendar.MONTH)+1)+"'"
					+" and day='"+cal.get(Calendar.DAY_OF_MONTH)+"'"
					+" and flag=1";
			cs = execute(query);
		}else if(queryName.equals("water2")){
			query = "SELECT * FROM "+DBbase.TABLE_WATER
					+" where flag=1"
					+" and server=0";
			cs = execute(query);
		}else if(queryName.equals("water_graph") && code != "" && code2 != ""){
	    	query = "SELECT * FROM "+DBbase.TABLE_WATER
					+" where year='"+code+"'"
					+" and month='"+code2+"'"
					+" and flag=1";
			cs = execute(query);
		}else if(queryName.equals("water_record") && code != "" && code2 != "" && code3 != ""){
	    	query = "SELECT * FROM "+DBbase.TABLE_WATER
					+" where year='"+code+"'"
					+" and month='"+code2+"'"
					+" and day='"+code3+"'"
					+" and flag=1";
			cs = execute(query);
		}else if(queryName.equals("alarm")){
			query = "SELECT * FROM "+AlarmDB.TABLE_ALARM;
			cs = execute2(query);
		}else if(queryName.equals("alarm2") && code != "" && code2 != "" ){
			query = "SELECT * FROM "+AlarmDB.TABLE_ALARM
					+" where hour='"+code+"'"
					+" and minute='"+code2+"'";
			cs = execute2(query);
		}else if(queryName.equals("alarm3") && code != ""){
			query = "SELECT * FROM "+AlarmDB.TABLE_ALARM
					+" where idx='"+code+"'";
			cs = execute2(query);
		}
		
		return cs;
		
	}

	
/**
 * =================================================================================
 * Assets 폴더의 db 파일 복사.
 * =================================================================================
 */
	
	@SuppressLint("SdCardPath")
	public static final String ROOT_DIR = "/data/data/com.dnbns.bodywater/databases/";  //로컬db 저장    
	private static final String DATABASE_NAME = "ALARM.db"; //로컬db명
	
	public void isCheckDB(){
		File file = new File(ROOT_DIR+DATABASE_NAME);
		
		if(file.exists()){
			file.delete();
		}
	}
	  
	public void getAssetDB(){
		isCheckDB();
		
		File folder = new File(ROOT_DIR);
		if (folder.exists()) {
		} else {
			folder.mkdirs();
		}
		
		AssetManager assetManager = mContext.getResources().getAssets();
		File outfile = new File(ROOT_DIR + DATABASE_NAME);
		InputStream is = null;
		FileOutputStream fo = null;
		long filesize = 0;
		try {
			is = assetManager.open(DATABASE_NAME, AssetManager.ACCESS_BUFFER);
			filesize = is.available();
			if (outfile.length() <= 0) {
				byte[] tempdata = new byte[(int) filesize];
				is.read(tempdata);
				is.close();
				outfile.createNewFile();
				fo = new FileOutputStream(outfile);
				fo.write(tempdata);
				fo.close();
			} else {
			}
		} catch (IOException e) {
		}
	}
	
	public static String addZero(int num){
		String temp = "";
		if(num < 10){
			temp = "0"+num;
		}else{
			temp = ""+num;
		}
		return temp;
	}
	
	
/**
 * =================================================================================
 * 한글 utf-8 변경 (url 전송전에 사용)
 * =================================================================================
 */

	public static String changeAnpercent(String s){
		String temp = null;
		try {
			temp = URLEncoder.encode(s, "utf-8");
//			temp = URLEncoder.encode(s, "euc-kr");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return temp;
	}
	

/**
 * =================================================================================
 * Error Dialog
 * =================================================================================
 */

	public void ErrorDialog(int code){
		AlertDialog.Builder dialog = new AlertDialog.Builder(mContext);
		switch (code) {
		case 9999:
			dialog.setMessage("오류가 발생하였습니다.");
			break;
		}
		dialog.setPositiveButton("ok", null);
		dialog.show();
	}
	
	public void ErrorDialog(String message){
		AlertDialog.Builder dialog = new AlertDialog.Builder(mContext);
		dialog.setMessage(message);
		dialog.setPositiveButton("ok", null);
		dialog.show();
	}

/**
 * =================================================================================
 * 서버통신 시작. (Post)
 * @return
 * =================================================================================
 * @throws UnsupportedEncodingException 
 * @throws IOException 
 * @throws XmlPullParserException 
 * @throws JSONException 
 */
	
	public JSONObject getHttpClient(JSONObject data)  {
		JSONObject json = null;
		Log.d(StaticValue.TAG, "서버통신 시작.");
		try {
						
			String url = data.getString("url");
		    ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
			if(!data.isNull("params")){
				Iterator<?> itr = data.getJSONObject("params").keys();
				while(itr.hasNext()){
					Object key = itr.next();
					nameValuePairs.add(new BasicNameValuePair((String) key, data.getJSONObject("params").get((String) key).toString()));
				}
			}
			
			Log.d("TEST", "url :"+url);
			Log.d("TEST", "params :"+nameValuePairs.toString());
			InputStream is = Connect(url, nameValuePairs);
			json = getJSONParser(is);
			
		} catch (Exception e) {
		    e.printStackTrace();
		}
		
		Log.d(StaticValue.TAG, "서버통신 종료.");
		return json;
	}
	
	//HttpClient 서버통신.
	private InputStream Connect(String url, ArrayList<NameValuePair> nameValuePairs){
	    HttpParams paramss = StaticValue.httpclient.getParams();
	    HttpConnectionParams.setConnectionTimeout(paramss, 5000);
	    HttpConnectionParams.setSoTimeout(paramss, 5000);

	    HttpPost httpPost = new HttpPost(url);
		try {
			UrlEncodedFormEntity entityRequest = new UrlEncodedFormEntity(nameValuePairs, "UTF-8");
			httpPost.setEntity(entityRequest);
		    
		    HttpResponse response = StaticValue.httpclient.execute(httpPost);
		    HttpEntity entityResponse = response.getEntity();
			return entityResponse.getContent();
		    
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}

	public JSONObject postHttp(String urls, String params)  {
		URL url;
		HttpURLConnection httpURLCon;
		JSONObject json = null;
		Log.d(StaticValue.TAG, "서버에 데이터 전송.");
		
		try {
			//URL 설정
			url = new URL(urls);
			//접속
			httpURLCon = (HttpURLConnection)url.openConnection();

			//전송모드 설정 - 기본적인 설정.
			httpURLCon.setDefaultUseCaches(false);
			httpURLCon.setDoInput(true);			//서버에서 읽기모드 지정
			httpURLCon.setDoOutput(true);			//서버에서 쓰기모드 지정
			httpURLCon.setRequestMethod("POST");	//전송방식 POST
			httpURLCon.setInstanceFollowRedirects(true);	//redicect 문제 해결
			
			//서버에게 웹에서 <Form>으로 값이 넘어온 것과 같은 방식으로 처리하라는 걸 알려준다.
			httpURLCon.setRequestProperty("content-type", "application/x-www-form-urlencoded");
			
			//서버에 값 전송.
			OutputStreamWriter outStream = new OutputStreamWriter(httpURLCon.getOutputStream(), "UTF-8");
			PrintWriter pw = new PrintWriter(outStream);
			pw.write(params);
			pw.flush();

			json = getJSONParser(openConnectionCheckRedirects(httpURLCon));
		} catch (MalformedURLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Log.d(StaticValue.TAG, "서버 데이터받기 완료");
		return json;
	}

	private InputStream openConnectionCheckRedirects(URLConnection c) throws IOException {
		boolean redir;
		int redirects = 0;
		InputStream in = null;
		do {
			if (c instanceof HttpURLConnection) {
				((HttpURLConnection) c).setInstanceFollowRedirects(false);
			}
			in = c.getInputStream();
			redir = false;
			if (c instanceof HttpURLConnection) {
				HttpURLConnection http = (HttpURLConnection) c;
				int stat = http.getResponseCode();
				if (stat >= 300 && stat <= 307 && stat != 306
						&& stat != HttpURLConnection.HTTP_NOT_MODIFIED) {
					URL base = http.getURL();
					String loc = http.getHeaderField("Location");
					URL target = null;
					if (loc != null) {
						target = new URL(base, loc);
					}
					http.disconnect();
					if (target == null
							|| !(target.getProtocol().equals("http") || target
									.getProtocol().equals("https"))
							|| redirects >= 5) {
						throw new SecurityException("illegal URL redirect");
					}
					redir = true;
					c = target.openConnection();
					redirects++;
				}
			}
		}
		while (redir);
		return in;
	}

/**
 * =================================================================================
 * JSON 파싱시작.
 * =================================================================================
 */
	
	public JSONObject getJSONParser(InputStream in) {
		Log.d(StaticValue.TAG, "JSON 파싱 시작.");
		
		JSONObject obj = null;
		StringBuilder jsonHtml = new StringBuilder();
		
		try {
			BufferedReader reader = new BufferedReader(new InputStreamReader(in, "UTF-8"));
			
			String line;
			while( (line = reader.readLine()) != null){
				jsonHtml.append(line+"\n");
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		Log.d("TEST", "json : "+jsonHtml.toString());
		
		try {
			obj = new JSONObject(jsonHtml.toString());
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		Log.d(StaticValue.TAG, "JSON 파싱 종료.");
		return obj;
	}
}
